package com.mark.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.mark.domain.Singer;
import com.mark.domain.Song;
import com.mark.util.DBUtil;

public class SongDao {


    public List<Song> getSongOptions(int author_id) {

        Connection conn = DBUtil.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Song> songs = new ArrayList<Song>();
        try {
            String sql = "SELECT s.id AS 'id', s.name AS 'name', a.id AS 'singer_id', a.name AS 'singer', s.description " +
                    "FROM singer as a INNER JOIN (SELECT * FROM song WHERE id <= 17 ORDER BY rand() LIMIT 4) AS s " +
                    "ON a.id = s.author_id WHERE a.id=?"; //目前只有17首歌有对应的文件
            ps = conn.prepareStatement(sql);
            ps.setInt(1, author_id);
            rs = ps.executeQuery();
            while (rs.next()) {
                Song s = new Song();
                s.setId(rs.getInt("id"));
                s.setName(rs.getString("name"));
                Singer singer = new Singer();
                singer.setId(rs.getInt("singer_id"));
                singer.setName(rs.getString("singer"));
                s.setAuthor(singer);
                s.setDescription(rs.getString("description"));
                songs.add(s);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.closeAll(conn, ps, rs);
        }
        return songs;
    }
}
